package com.mrzhou.poi;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * @author qiang.zhou
 */
public class XlsxParse {

    public static void main(String[] args) throws Exception {
        BufferedReader reader = new BufferedReader(new BufferedReader(new FileReader("C:/Users/qiang.zhou/Desktop/common_texts_all.properties")));
        String line;
        Map<String, String> map = new LinkedHashMap<>();
        while((line = reader.readLine()) != null) {
            int split = line.indexOf("=");
            if(split + 1 < line.length()) {
                map.put(line.substring(0, split).toUpperCase(), line.substring(split + 1));
            }
        }

//        Properties properties = new Properties();
//        properties.load(new BufferedReader(new BufferedReader(new FileReader("C:/Users/qiang.zhou/Desktop/common_texts_all.properties"))));

        XSSFWorkbook book = new XSSFWorkbook(new FileInputStream("C:/Users/qiang.zhou/Desktop/消逝的光芒物品代码.xlsx"));
        for (int i = 0; i < book.getNumberOfSheets(); i++) {
            XSSFSheet sheet = book.getSheetAt(i);
            int totalRow = sheet.getLastRowNum();
            for(int j = 2; j <= totalRow; j++) {
                XSSFRow row = sheet.getRow(j);
                XSSFCell cell = row.getCell(2);
                String key = cell.getStringCellValue() + "_N";
                XSSFCell valueCell = row.getCell(3);
                String value = map.get(key.toUpperCase());
                if(StringUtils.isNotBlank(value)) {
                    valueCell.setCellValue(value);
                } else {
                    System.out.println(key);
                }
            }
        }

        book.write(new FileOutputStream("C:/Users/qiang.zhou/Desktop/消逝的光芒物品代码.xlsx"));

    }


}
